<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- saved from url=(0064)http://www.ugrad.cs.ubc.ca/~cs304/2013S2/project/p3/p3-desc.html -->
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  
  <meta name="ProgId" content="FrontPage.Editor.Document">
  <meta name="Generator" content="Microsoft FrontPage 6.0">
  <meta name="Originator" content="Microsoft Word 9">
  <title>Project Part III</title>
  <style>
<!--
 /* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;
	mso-font-charset:2;
	mso-generic-font-family:auto;
	mso-font-pitch:variable;
	mso-font-signature:0 268435456 0 0 -2147483648 0;}
 /* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{mso-style-parent:"";
	margin:0in;
	margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:12.0pt;
	font-family:"Times New Roman";
	mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;
	text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;
	text-underline:single;}
p
	{font-size:12.0pt;
	font-family:"Times New Roman";
	mso-fareast-font-family:"Times New Roman";}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;
	mso-paper-source:0;}
div.Section1
	{page:Section1;}
 /* List Definitions */
@list l0
	{mso-list-id:76099457;
	mso-list-type:hybrid;
	mso-list-template-ids:1633446824 1806361872 1437111066 -829809306 1424634100 1617037056 -458182038 -127765686 1869405388 972343590;}
@list l0:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l1
	{mso-list-id:513766373;
	mso-list-type:hybrid;
	mso-list-template-ids:-401191754 1066989626 1348614164 -444974236 -860867312 -164847790 -1721341600 -142949750 -1487759800 -875759782;}
@list l1:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l2
	{mso-list-id:681586228;
	mso-list-type:hybrid;
	mso-list-template-ids:-2021602232 -1845754582 -1834293770 -1758428258 -1196364194 802354944 1934799504 -506189322 1305373962 -2103165068;}
@list l2:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l3
	{mso-list-id:718210372;
	mso-list-type:hybrid;
	mso-list-template-ids:2128275700 1471808416 -1814536942 240698526 2093674580 1841735660 2067299846 578966964 1444287058 1094986604;}
@list l3:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l4
	{mso-list-id:1005551130;
	mso-list-type:hybrid;
	mso-list-template-ids:531390266 -1755265622 -1223116608 584118208 1339583988 -2088584480 -793192826 -1835598624 -428566934 -397275248;}
@list l4:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l5
	{mso-list-id:1242834642;
	mso-list-type:hybrid;
	mso-list-template-ids:-621907100 1588893342 -1281467116 1064704648 497478022 1760882228 -653365606 -1455624074 2079109970 -530703110;}
@list l5:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l6
	{mso-list-id:2102946051;
	mso-list-type:hybrid;
	mso-list-template-ids:-481912402 610418892 1503954466 1853544344 -1981755028 -1021380428 -1069634600 -197468302 -1040962306 1639323458;}
@list l6:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
-->
  </style><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext="edit">
  <o:idmap v:ext="edit" data="1"/>
 </o:shapelayout></xml><![endif]-->
  <meta name="Template" content="C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\html.dot">
<style type="text/css"></style></head>
<body bgcolor="white" lang="EN-CA" link="blue" vlink="purple">
<h2 align="center" style="text-align: center;">CPSC 304</h2>
<h2 align="center" style="text-align: center;">Project Part III </h2>
<h2 align="center" style="text-align: center;">Partial Implementation </h2>
<p style=""><b><span style="font-size: 13.5pt;">Due: Wednesday, August 7, at 24:00.&nbsp; 
</span></b></p>
<b><span style="font-size: 13.5pt;">Project Presentations: Thursday, August 8th<o:p>.</o:p></span></b><p></p>
<p>In this &nbsp;part of the project, you will&nbsp; implement a part
of the database
design for the AMS store and define a user &nbsp;friendly
graphical interface for the
system using Java and JDBC. Again, we would like all the groups&nbsp;
to
implement the same design. Therefore, we ask you to use the tables
which are
listed in the next section. </p>
<p>To implement the system you need to </p>
<ul type="disc">
  <li class="MsoNormal" style="">define the tables, </li>
  <li class="MsoNormal" style="">write code for the transactions, and </li>
  <li class="MsoNormal" style="">design a reasonable user interface. </li>
</ul>
<p>Usually, the tables are &nbsp;created by the database administrator using an 
interactive SQL command interpreter For Oracle, this is the tool&nbsp; SQL*Plus that 
you used in the lab. Usually, we put the table definitions in a text file and 
"source" it using the related Oracle command, as it is explained in the 
tutorial.</p>
<p>The code for the transactions is usually broken down into a set of classes 
and methods in the host language (in our case, in Java). The user interface is 
built on top of that. This approach offers great modularity and flexibility. For 
instance, you can easily add more functions to the system or completely change 
the interface in the future. The Java/JDBC Tutorials describe how to use Java 
and JDBC with the Oracle server. You should study the corresponding set of 
tutorials before you go to your labs and follow the examples we have developed 
there.<span style="">&nbsp; </span></p>
<p>Tables can also be defined using the JDBC API. Because
this part will be done only once, when the initial database is created,
the
code for the table definitions is usually placed in a separate program
that is
run once. In this part you can follow any approach. Just make sure to
print
&nbsp;all the code you write and submit it for marking. </p>
<p>&nbsp; </p>
<p><b><u>Tables for Part III</u></b> </p>
<p>You should provide complete definitions for the following tables: </p>
<ul type="disc">
  <li class="MsoNormal" style=""><i>Item </i>(<u>upc</u>, title, type, category, 
	company, year, price, stock) </li>
  <li class="MsoNormal" style=""><i>LeadSinger </i>(<u>upc</u>, <u>name</u>) </li>
  <li class="MsoNormal" style=""><i>HasSong </i>(<u>upc</u>, <u>title</u>) </li>
	<li class="MsoNormal" style=""><i>Purchase </i>(<u>receiptId,</u> date, cid, card#, expiryDate, expectedDate, deliveredDate)
	</li>
	<li class="MsoNormal" style=""><i>PurchaseItem </i>(<u>receiptId</u>,&nbsp; 
	<u>upc</u>, 
	quantity) </li>
	<li class="MsoNormal" style=""><i>Customer </i>(<u>cid</u>, password, name, address, 
	phone)
	</li>
	<li class="MsoNormal" style=""><i>Return </i>(<u>retid</u>, date, receptId) 
	</li>
	<li><i>ReturnItem</i>(<u> retid</u>, <u>upc</u>, quantity) </li>
</ul>
<p><b>Note:</b>&nbsp;
</p>
<ul>
  <li>Only the primary keys are shown for the tables. Check the solution for 
	part 2 to find out if there are other constraints that are applicable to 
	these tables.</li>
	<li>From the Item hierarchy, we have decided&nbsp; to keep only the CD's and 
	DVD's. We ignore books and scores. Therefore, the Item table contains only 
	CD's and DVD's. All items are taxable.</li>
	<li>In the Item table, type can be <i>cd</i> or <i>dvd</i> and the caregory 
	has one of the following values:&nbsp; <i>rock, pop, rap, country, 
	classical, new age</i> and <i>instrumental</i>. </li>
	<li>We have only ONE STORE. Therefore we don't need the Store Stored tables. 
	The item table has a stock attribute to keep track of the copies of each 
	item the store has. Customers can still buy on line or in store as before.</li>
  <li>We don't keep neither the Supplier nor the Supplies table (i.e. we don't 
	store the supplier information in this database and we don't keep track who supplies 
	each item). We also have eliminated the the Shipment and the&nbsp; ShipItem&nbsp; tables. 
	We won't have shipments. Instead we'll have a transaction to add an item in 
	the store.</li>
	<li>An expectedDate attribute was added to the Purchase table.&nbsp; This 
	indicates the date we promised to deliver an order. The deliveredDate 
	attribute shows the actual date of the delivery. These attributes are both 
	null for an in-store purchase.</li>
</ul>
<p>&nbsp;&nbsp;</p>
<span style="font-family: &quot;times new roman&quot;;"></span>
<p><b><u>Operations for Part III</u></b> </p>
<p>It may be convenient to define the following operations for each table&nbsp; : </p>
<ul type="disc">
  <li class="MsoNormal" style=""><i>insert </i>a tuple into the table </li>
  <li class="MsoNormal" style=""><i>delete </i>a tuple from the table </li>
  <li class="MsoNormal" style=""><i>display </i>all the rows of the
table (you need this for debugging and demonstration). </li>
</ul>
<p>These operations will be very useful for debugging your system and also for 
demonstrating your project.&nbsp; Insert operations require values for the components of a tuple.
These
operations check for duplicates and insert a new tuple in the table.
Delete
operations require the key for a tuple and delete that tuple from the
table.
These operations will be used by the more complex transactions you are
going to define next. Some insert operations need to generate new
values for a key (like the key
of a purchase record). The tutorials should have some information on
that.&nbsp; </p>
<p>After the tables have been created and the basic operations have
been
defined, you should write code for the following transactions. </p>
<p>Transactions performed by a clerk:
</p>
<ul type="disc">
<li>process a purchase of&nbsp; items in the store </li>
  <li>process a return of an item for refund&nbsp; </li>
</ul>
<ul type="disc">
</ul>
<p>Transactions performed by a customer:
</p>
<ul type="disc">
<li>registration (if it is the first time accessing the system)</li>
<li>purchase of&nbsp; items online</li>
</ul>
<p>Transactions performed by a manager :</p>
<ul>
	<li>add items to the store; this will normally add a number of copies of the 
	same item 
  </li>
	<li>process the delivery of an order (it just sets the delivery date)</li>
  <li> daily sales report
  </li>
  <li>top selling items</li>
</ul>
<p>
The behavior of these operations is as following:<br>
</p>
<ul>
  <li><span style="text-decoration: underline">In Store Purchase</span>: A
customer can purchase a number of items and pay by&nbsp; cash or a
credit card.&nbsp; When the customer requests a credit card charge, the clerk 
	requests authorization from the credit card company (this is NOT part of the 
	system). If authorization fails the customer is asked to pay cash. If the 
	customer refuses, the purchase is cancelled.&nbsp; When the payment is 
	completed the system prints a receipt that shows, a receipt number, the 
	date, a list with the items purchased, their quantities and their prices, 
	and the total amount for the purchase.&nbsp; If the customer pays by a 
	credit card, the receipt should show the last 5 digits of the card's number.<br>
&nbsp; 
  </li>
	<li><u>Online Order:</u>&nbsp; To buy online, customers have to identify 
	themselves&nbsp; by typing their customer id and password.&nbsp; Customers 
	who access the store online for the first time have to register first.&nbsp; 
	To shop at the site, registered customers will be given a virtual shopping 
	basket and asked to specify the&nbsp; item they want to buy. The customer 
	will describe the item by providing the category, or the title, or the 
	leading singer (or all of them), &nbsp;and the quantity. If the information is 
	not enough to define a single item, the system will display all the items 
	that match the input and ask the customer to select one.&nbsp; When an item 
	is selected, it will be added to the customer shopping cart. Each time an 
	item is selected the system has to make sure that there is enough quantity 
	in the store to complete the purchase. Otherwise the system will ask the 
	customer to accept the existing quantity.&nbsp;&nbsp; The customer can 
	repeat the same process for any number of items.&nbsp;&nbsp; When the 
	customer is ready to check-out, the system will produce a bill with the 
	items and the total amount. The client has to provide a credit card number 
	and expiry date to complete the transaction.&nbsp; After that,&nbsp; the 
	system will create a purchase for the store and inform the customer about 
	the number of days it will take to receive the goods.&nbsp; This number is 
	estimated by the number of outstanding orders and&nbsp; the maximum number 
	of orders that can be delivered in a day (which is fixed; you need to decide 
	on this). <br>&nbsp; </li>
	<li><u>Customer Registration.</u> Customers who access the store online for 
	the first time, will be asked to register by providing their personal 
	information, including their name, their address, phone 
	number,&nbsp; an id and a password.&nbsp;&nbsp; If the id is already in the system, they will be asked 
	to provide another one.&nbsp;<br>
	<br></li>
	<li><span style="text-decoration: underline;">Return</span>: Customers can 
	return merchandises to the store within 15 days from the purchase date. When 
	an item is returned, the system verifies the receipt and issues a refund in 
	the same &nbsp;payment form that was used for the original purchase. That is, &nbsp;if 
	cash was used for the original purchase, the refund is in cash. Otherwise, 
	the credit card&nbsp; is credited (which is not part of this system). <br>&nbsp; <br></li>
	<li><span style="text-decoration: underline">Adding Items</span>:&nbsp;
It adds new copies of an item.&nbsp; The user has to specify the item's upc&nbsp; the quantity and the unit 
	price (optional).&nbsp; If a unit price is specified this will be the new 
	price for this item and it will override any old price that may exist for this item. 
	If no new unit price is provided the old unit price will be retained.<br></li>
</ul>
<ul>
  <li><u>Daily Sales Report</u>: The user provides a date and the
system creates a report with&nbsp;the items that were sold on that
date.&nbsp;
    <p>For each item the report shows the &nbsp;item's upc, category,
selling price, number of &nbsp;pieces sold and the total value for the
sold items. &nbsp;The records should be grouped by the category and
show the total sales value for each category (each category may contain CD's and 
	DVD's; we don't separate them ). At the end, the report
should show the total sales value for the day. The output may look like
the following:<br>
&nbsp; &nbsp; &nbsp; <br>
    <table cellpadding="2">
      <tbody>
        <tr>
          <td>UPC</td>
          <td>Category</td>
          <td>Unit Price</td>
          <td>Units</td>
          <td>Total Value</td>
        </tr>
        <tr>
          <td>2244</td>
          <td>classical</td>
          <td>10.50</td>
          <td>&nbsp;10</td>
          <td>105.00</td>
        </tr>
        <tr>
          <td>2555</td>
          <td>classical</td>
          <td>5.00</td>
          <td>&nbsp;&nbsp; 5</td>
          <td>&nbsp; 25.00</td>
        </tr>
        <tr>
          <td>3455</td>
          <td>classical</td>
          <td>20.00</td>
          <td>&nbsp;&nbsp; 1</td>
          <td>&nbsp; 20.00</td>
        </tr>
        <tr>
          <td><br>
          </td>
          <td>Total</td>
          <td><br>
          </td>
          <td>&nbsp;16</td>
          <td>150.00</td>
        </tr>
        <tr>
          <td>1255</td>
          <td>pop</td>
          <td>20.00</td>
          <td>&nbsp;&nbsp; 5</td>
          <td>100.00</td>
        </tr>
        <tr>
          <td>5666</td>
          <td>pop</td>
          <td>10.00</td>
          <td>&nbsp;10</td>
          <td>100.00</td>
        </tr>
        <tr>
          <td><br>
          </td>
          <td>Total</td>
          <td><br>
          </td>
          <td>&nbsp;15</td>
          <td>200,00</td>
        </tr>
        <tr>
          <td>1244</td>
          <td>rock</td>
          <td>2.50</td>
          <td>&nbsp;20</td>
          <td>&nbsp; 50.00</td>
        </tr>
        <tr>
          <td>8844</td>
          <td>rock</td>
          <td>5.00</td>
          <td>&nbsp;50</td>
          <td>250.00</td>
        </tr>
        <tr>
          <td><br>
          </td>
          <td>Total</td>
          <td><br>
          </td>
          <td>&nbsp;70</td>
          <td>300.00</td>
        </tr>
        <tr>
          <td><br>
          </td>
          <td><br>
          </td>
          <td><br>
          </td>
          <td><br>
          </td>
          <td>----------</td>
        </tr>
        <tr>
          <td><br>
          </td>
          <td>Total Daily</td>
          <td>Sales</td>
          <td>101</td>
          <td>650.00</td>
        </tr>
      </tbody>
    </table>
    </p>
    <p> &nbsp;&nbsp;&nbsp; </p>
  </li>
  <li> <u>Top Selling Items</u>: The user
provides a date and a number, say n. The system prints a list of the n
best selling items on that day. For each best seller, the system shows
the title, the company, the current stock and the number of copies
sold. The output should be ordered according to
sales: the best selling item should be first, the second best will
follow, etc.<br>
  </li>
</ul>
<p>For each transaction,&nbsp; make sure to check for the basic errors
and
produce
appropriate messages for the user.
</p>
<p></p>
<p><b><u>User Interface</u></b>
</p>
<p>Create an easy to use GUI&nbsp; that allows the users to
execute all the operations and transactions provided by the system,&nbsp;and
display the rows of any table. Make sure to design
your interface
in such a way that all the error messages appear in separate pop-up
dialogue windows,
or in a designated area of the main window, so that they don't
interfere
with the other activities.
</p>
<p>Do not worry about access control. Your interface should have at
least three menus: one for the clerks one for the customers, and&nbsp; another for the managers.
Nevertheless, your program should not provide any access control. It
should let any user access any one of the menus.
</p>
<p></p>
<p><u><b>Submission and </b></u>
<b><u>Project Demonstration</u></b>
</p>
<p>Test your system with enough data to make sure that works
well.&nbsp; GUIs normally require a lot of time and multiple tries to
get them right. We
expect you to produce a simple but adequate interface. You should not
spend a
lot of time on the interface itself. You should concentrate on the
coding of the functionality of your project rather than the GUI. The
main goal
of the project is to give you some experience with SQL and JDBC.
</p>
<p><b>For Part III, you should hand in:</b>
</p>
<ul>
  <li> A brief introduction of the system and a brief user manual. </li>
  <li> A listing of the code for the creation of the tables. </li>
  <li> A listing of the code for the operations, the transactions and
your interface </li>
</ul>
<p>The person from each group who will hand in this part should do the 
following:</p>
<ul>
	<li>
	create a 
	directory<b> p3 </b>inside your <b>cs304</b> directory</li>
	<li>
	
	place the project files <span style="">&nbsp;</span>(the title file, <span style="">
	&nbsp;user manual</span>, which should be in html or pdf form, and the code 
	files) <span style="">&nbsp;&nbsp;</span>in <b>p3.</b></li>
	<li>
	
	<!--[endif]-->hand it in using: <br>
	<span style="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>&gt; <b>handin cs304 p3</b></li>
</ul>
<p>To evaluate your project better, we'll ask each group to demonstrate
their system. Demonstrations will be scheduled for 20-30&nbsp; on the
day shown at the top of this file.&nbsp; We'll pass a time sheet around in the lecture&nbsp; for the
groups to book their presentation
times. The mark you will receive for this
part of the project will be based on the quality of your presentation,
the functionality of your program, the overall design, and
the design of your interface. Each member of the group should come to
the presentation prepared with a demonstration plan.
&nbsp; </p>
<p>Finally, this part of the project is worth 50% of the total project mark. 
Each of part I and II&nbsp; is worth 25% of the total project mark.&nbsp;</p>


</body><style type="text/css">embed[type*="application/x-shockwave-flash"],embed[src*=".swf"],object[type*="application/x-shockwave-flash"],object[codetype*="application/x-shockwave-flash"],object[src*=".swf"],object[codebase*="swflash.cab"],object[classid*="D27CDB6E-AE6D-11cf-96B8-444553540000"],object[classid*="d27cdb6e-ae6d-11cf-96b8-444553540000"],object[classid*="D27CDB6E-AE6D-11cf-96B8-444553540000"]{	display: none !important;}</style></html>